<?php

/* * ***********************************************************************
  Class Pick_Core_Model
  ----------------------------------------------------------------
  PICKCOCMS Project
  Company: PICKCO  Co., Ltd
  Name: Nguyen Hai Nam
  Last updated: 27/09/2009
 * ************************************************************************ */
/* Edit log:
  - 29/09/2009 16208 - Hai Nam: Update 'select' function
  - 28/09/2009 07:30 - Hai Nam: Initialize
 */

class Pick_Core_Model {

    var $_db;
    var $table = '';
    var $fields = array();

    /* -----------------------------------------------------------------------*
     * Function: Constructor
     * Parameter: DB , Table, Fields
     * Return: No return
     * ----------------------------------------------------------------------- */

    function Pick_Core_Model($db = '', $table = '', $fields = array()) {
        $this->_db = $db;
        $this->table = $table;
        $this->fields = $fields;
    }

    /* -----------------------------------------------------------------------*
     * Function: Select
     * Parameter: Condition, Order, Limit, Value
     * Return: Return an array
     * ----------------------------------------------------------------------- */

    function select($fields = '*', $condition = '1>0', $sort = array(), $start = '0', $limit = '') {
        $sql = 'SELECT DISTINCT ' . $fields . ' FROM `' . $this->table . '` WHERE ' . $condition;
        $order_sql = '';
        if ($sort) {
            $order_sql = ' ORDER BY ';
            $i = 0;
            foreach ($sort as $field => $order) {
                $order_sql .= "$field $order" . ($i < count($order) - 1 ? ',' : '');
                $i++;
            }
        }
        $sql .= $order_sql . ($limit ? " LIMIT $start,$limit" : '');

        $result = $this->_db->query($sql);
        if ($result) {
            $data = array();
            while ($row = $this->_db->fetchArray($result)) {
                $data[] = $row;
            }
            $this->_db->freeResult($result);
            return $data;
        }
        return 0;
    }

    /* -----------------------------------------------------------------------*
     * Function: SelectMutilTable
     * Parameter: Condition, Order, Limit, Value
     * Return: Return an array
     * ----------------------------------------------------------------------- */

    function selectMutilTable($fields = '*', $strTable = '', $condition = '1>0', $sort = array(), $start = '0', $limit = '') {
        if ($strTable == '')
            $strTable = $this->table;
        $sql = 'SELECT DISTINCT ' . $fields . ' FROM ' . $strTable . ' WHERE ' . $condition;
        $order_sql = '';
        if ($sort) {
            $order_sql = ' ORDER BY ';
            $i = 0;
            foreach ($sort as $field => $order) {
                $order_sql .= "$field $order" . ($i < count($order) - 1 ? ',' : '');
                $i++;
            }
        }
        $sql .= $order_sql . ($limit ? " LIMIT $start,$limit" : '');

        $result = $this->_db->query($sql);
        if ($result) {
            $data = array();
            while ($row = $this->_db->fetchArray($result)) {
                $data[] = $row;
            }
            $this->_db->freeResult($result);
            return $data;
        }
        return 0;
    }

    /* -----------------------------------------------------------------------*
     * Function: showTable
     * Parameter: Condition, Order, Limit, Value
     * Return: Return an array
     * ----------------------------------------------------------------------- */

    function showTables() {
        $sql = "SHOW TABLES ";
        $result = $this->_db->query($sql);
        if ($result) {
            $data = array();
            while ($row = $this->_db->fetchArray($result, 0)) {
                $data[] = $row[0];
            }
            $this->_db->freeResult($result);
            return $data;
        }
        return 0;
    }

    /* -----------------------------------------------------------------------*
     * Function: showTable
     * Parameter: Condition, Order, Limit, Value
     * Return: Return an array
     * ----------------------------------------------------------------------- */

    function showColumns($nameTable) {
        $sql = "SHOW COLUMNS FROM $nameTable";
        $result = $this->_db->query($sql);
        if ($result) {
            $data = array();
            while ($row = $this->_db->fetchArray($result)) {
                $data[] = $row;
            }
            $this->_db->freeResult($result);
            return $data;
        }
        return 0;
    }
    
    function showTypeColumns($nameTable) {
        $sql = "SHOW COLUMNS FROM $nameTable";
        $result = $this->_db->query($sql);
        if ($result) {
            $data = array();
            while ($row = $this->_db->fetchArray($result)) {
                $tempType = explode('(', $row['Type']);
                $data[$row['Field']] = $tempType[0];
            }
            $this->_db->freeResult($result);
            return $data;
        }
        return 0;
    }
    
    /* -----------------------------------------------------------------------*
     * Function: formatData
     * Parameter: fields
     * Return: array field was formated
     * ----------------------------------------------------------------------- */
    function formatData($fields = ""){
        $typeColumn = $this->showTypeColumns($this->table);
        if(empty($fields)){
            $fields = $this->fields;
        }
        if(isset($fields['id'])){
            unset($fields['id']);
        }
        foreach($fields as $nameField=>$value){
            $type = $typeColumn[$nameField];
            switch($type){
                case 'int':
                case 'tinyint':{
                    $fields[$nameField] = is_numeric($value)?$value:0;
                }break;
                case 'date':{
                    $fields[$nameField] = $value!=""?$value: date('Y-m-d');
                };break;
                default:
                    $fields[$nameField] = $value!=""?$value:"";
            }
        }
        return $fields;
    }
    /* -----------------------------------------------------------------------*
     * Function: Add
     * Parameter: fields, primary key, primary value (pass -1 to use the value from fields array
     * Return: New insert ID if OK, 0 if failed
     * ----------------------------------------------------------------------- */

    function add($fields = '', $pk = 'id', $pkValue = 'NULL') {
        if (!$fields)
            $fields = $this->fields;
        $fields = $this->formatData($fields);
        $numFields = count($fields);
        if ($numFields) {
            $sql = "INSERT INTO `" . $this->table . "`";
            $fieldList = '';
            $valueList = '';
            $i = 0;
            foreach ($fields as $fieldName => $fieldValue) {
                $fieldList .= "`$fieldName`";
                $valueList .= "'" . ($pk == $fieldName ? ($pkValue == -1 ? mysql_real_escape_string($fieldValue) : $pkValue) : mysql_real_escape_string($fieldValue)) . "'";
                if ($i < $numFields - 1) {
                    $fieldList .= ',';
                    $valueList .= ',';
                }
                $i++;
            }
            $sql .= " ($fieldList) VALUES ($valueList)";
            if ($this->_db->query($sql))
                return $this->_db->getInsertId();
        }
        return 0;
    }

    /* -----------------------------------------------------------------------*
     * Function: Delete
     * Parameter: condition
     * Return: 1 if OK, 0 if failed
     * ----------------------------------------------------------------------- */

    function delete($condition = '1<0') {
        $sql = "DELETE FROM `" . $this->table . "` WHERE $condition";
        if ($this->_db->query($sql))
            return 1;
        return 0;
    }

    /* -----------------------------------------------------------------------*
     * Function: Update
     * Parameter: fields and condition
     * Return: 1 if OK, 0 if failed
     * ----------------------------------------------------------------------- */

    function update($fields = '', $condition = '1<0') {
        if (!$fields)
            $fields = $this->fields;
        $fields = $this->formatData($fields);
        $numFields = count($fields);
        if ($numFields) {
            $sql = "UPDATE `" . $this->table . "` SET ";
            $i = 0;
            foreach ($fields as $fieldName => $fieldValue) {
                $sql .= "`$fieldName` = '" . mysql_real_escape_string($fieldValue) . "'" . ($i < $numFields - 1 ? ',' : '');
                $i++;
            }
            $sql .= " WHERE $condition";
            if ($this->_db->query($sql))
                return 1;
        }
        return 0;
    }

    /* -----------------------------------------------------------------------*
     * Function: Get number of records and pages
     * Parameter: pk, condition and items per page
     * Return: pages, rows
     * ----------------------------------------------------------------------- */

    function getNumItems($pk = 'id', $condition = '1>0', $items_per_page = 0) {
        $rows = 0;
        $pages = 1;
        $return = array();
        $sql = "SELECT COUNT(`$pk`) FROM `" . $this->table . "` WHERE $condition";
        if ($this->_db->query($sql))
            $rows = $this->_db->fetchRow();
        if ($rows) {
            $pages = ceil($rows[0] / $items_per_page);
            $return = array('rows' => $rows[0], 'pages' => $pages);
            return $return;
        }
        return 0;
    }

    /* -----------------------------------------------------------------------*
     * Function: Get number of records and pages
     * Parameter: pk, condition and items per page
     * Return: pages, rows
     * ----------------------------------------------------------------------- */

    function getListFields() {
        $sql = "SHOW COLUMNS FROM " . $this->table;
        $result = $this->_db->query($sql);
        if ($result) {
            $data = array();
            while ($row = $this->_db->fetchArray($result)) {
                $data[] = $row['Field'];
            }
            $this->_db->freeResult($result);
            return $data;
        }
        return 0;
    }

}

?>